package model;

import java.sql.*;

import java.util.*;

import javax.naming.NamingException;

import db.DbConnectionPool;
import db.DbException;

public class UniversityModel {
	private static String insertStr = "SELECT DISTINCT location FROM Universities ";
	private static String insertStr2 = "SELECT name FROM Universities WHERE location=?";
	
	
	public static Vector<String> getLocation() throws DbException{
		try{
		Connection conn = DbConnectionPool.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(insertStr);
		
		Vector<String> location = new Vector<String>();
        while (rs.next()) {
                location.add(rs.getString("location"));
        }

        rs.close();
        stmt.close();
        conn.close();
        return location;
        
		} catch (SQLException ex) {
            throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	 
	
	
	public static Vector<String> getUniversities(String location) throws DbException{
		try{
			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(insertStr2);
			ResultSet rs = null;
			pstmt.setString(1, location);
			rs = pstmt.executeQuery();

			Vector<String> universities = new Vector<String>();
			while (rs.next()) {
                universities.add(rs.getString("name"));
			}

			rs.close();
			pstmt.close();
			conn.close();
			return universities;
        
		} catch (SQLException ex) {
            throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
}


